package jdbc;

import human.User;

import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

public class UserDao extends DataAccessObject {
	private static final Logger log = Logger.getLogger(DataAccessObject.class);
	private static final String PREFIX = "US_";
	private static final String TABLE = " ElUser ";
	private final Class<?> objectClass = User.class;

	public ArrayList<User> selectUsersByOwnerId(Long ownerId){
		ArrayList<User> users = null;
		conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn.prepareStatement("SELECT * FROM " + TABLE + 
			" WHERE (us_ownerId) = (?) order by " + PREFIX + "id DESC");
			prepStat.setLong(1, ownerId.longValue());
			prepStat.execute();
			ResultSet res = prepStat.getResultSet();
			users = getUsersFromResultSet(res);

		} catch (SQLException e) {
			log.error("In selectUserByOwnerId sql ex " + e);
		} catch (SecurityException e) {
			log.error("In selectUserByOwnerId security ex  " + e);
		} catch (IllegalArgumentException e) {
			log.error("In selectUserByOwnerId ill arg ex  " + e);
		}  finally {
			DataAccessObject.silentClose(conn);
		}
		return users;
		
	}

	private ArrayList<User> getUsersFromResultSet(ResultSet res)  {
		
		ArrayList<User> users;
		users = new ArrayList<User>();
		Field[] fields = objectClass.getDeclaredFields();
		AccessibleObject.setAccessible(fields, true);
	
		try {
			while (res.next()) {
				User user = new User();
				for (Field f : fields) {
					setFieldFromResultSet(res, user, PREFIX, f);
				}
				users.add(user);
			}

		} catch (SQLException e) {
			log.error("In getUsersFromResultSet sql ex  " + e);
		} catch (NoSuchMethodException e) {
			log.error("In getUsersFromResultSet no such meth ex  " + e);
		} catch (IllegalAccessException e) {
			log.error("In getUsersFromResultSet ill access ex  " + e);
		} catch (InvocationTargetException e) {
			log.error("In getUsersFromResultSet inv targ ex  " + e);
		}
		return users;
	}
	
	public ArrayList<User> selectAllUsers() {

		ArrayList<User> users = null;
		conn = null;
		CallableStatement callStat = null;
		try {
			conn = getConnection();
			String sql = "SELECT * FROM " + TABLE + "order by " + PREFIX + "id";
			callStat = conn.prepareCall(sql);
			log.trace(sql);
			callStat.execute();
			ResultSet res = callStat.getResultSet();
			
			users = getUsersFromResultSet(res);
			
		} catch (SQLException e) {
			log.error("In select all users " + e);
		} catch (SecurityException e) {
			log.error("In select all users " + e);
		} catch (IllegalArgumentException e) {
			log.error("In select all users " + e);
		} finally {
			DataAccessObject.silentClose(conn);
		}
		return users;
	}

	public void insertUser(User user) {
		Connection conn = null;
		if (user == null) {
			log.warn("Tried to insert null user!");
			return;
		}
		if (user.getName() == null) {
			log.warn("Tried to insert a user with a null name!");
			return;
		}
		if (user.getId() != null && selectUserById(user.getId()) != null) {
			updateUser(user);
			log.debug("Tryied to insert a user with a null name!");
		} else {
			try {
				if (user != null && user.getEmail() != null) {
					conn = getConnection();
					PreparedStatement prepStat = null;
					prepStat = conn
							.prepareStatement("INSERT INTO " + TABLE + " ( us_ownerId, us_name, us_birthDate,"
									+ "us_email, us_password, us_degree, us_cardCode,  "
									+ "us_right) VALUES (?,?,?,?,?,?,?,?)");

					if (user.getOwnerId() == null) {
						prepStat.setString(1, null);
					} else {
						prepStat.setLong(1, user.getOwnerId());
					}
					prepStat.setString(2, user.getName());
					if (user.getBirthDate() == null) {
						prepStat.setString(3, null);
					} else {
						prepStat.setLong(3, user.getBirthDate());
					}
					prepStat.setString(4, user.getEmail());
					prepStat.setString(5, user.getPassword());
					prepStat.setString(6, user.getDegree());
					prepStat.setString(7, user.getCardCode());
					if (user.getRight() == null) {
						prepStat.setString(8, null);
					} else {
						prepStat.setShort(8, user.getRight());
					}

					prepStat.execute();
				}
			} catch (SQLException e) {
				log.error("In insert user ", e);
			} finally {
				DataAccessObject.silentClose(conn);
			}
		}
	}

	public User selectUserById(Long id) {

		if (id == null) {
			log.warn("Null id in select user by id.");
			return null;
		}

		User user = null;
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn.prepareStatement("SELECT * FROM " + TABLE + " WHERE (us_id) = (?);");

			prepStat.setLong(1, id.longValue());

			prepStat.execute();
			ResultSet res = prepStat.getResultSet();
			if (res.first()) {
				Field[] fields = objectClass.getDeclaredFields();
				AccessibleObject.setAccessible(fields, true);
				user = new User();
				for (Field f : fields) {
					setFieldFromResultSet(res, user, PREFIX, f);
				}
			}
		} catch (SQLException e) {
			log.error("In select User By Id", e);
		} catch (NoSuchMethodException e) {
			log.error("In select User By Id", e);
		} catch (IllegalAccessException e) {
			log.error("In select User By Id", e);
		} catch (InvocationTargetException e) {
			log.error("In select User By Id", e);
		} finally {
			DataAccessObject.silentClose(conn);
		}
		return user;
	}

	public  User selectUserByEmail(String email) {
		if (email == null) {
			log.warn("NULL email in select user by email!");
			return null;
		}

		User user = null;
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn.prepareStatement("SELECT * FROM " + TABLE + " WHERE (us_email) = (?);");

			prepStat.setString(1, email);
			prepStat.execute();
			ResultSet res = prepStat.getResultSet();
			if (res.first()) {
				Field[] fields = objectClass.getDeclaredFields();
				AccessibleObject.setAccessible(fields, true);
				user = new User();
				for (Field f : fields) {
					setFieldFromResultSet(res, user, PREFIX, f);
				}
			}
		} catch (SQLException e) {
			log.error("in select User by email", e);
		} catch (NoSuchMethodException e) {
			log.error("in select User by email", e);
		} catch (IllegalAccessException e) {
			log.error("in select User by email", e);
		} catch (InvocationTargetException e) {
			log.error("in select User by email", e);
		} finally {
			DataAccessObject.silentClose(conn);
		}
		return user;
	}

	public User selectUserByEmailAndPassword(String email, String password) {
		if (email == null || password == null || "".equals(email) || "".equals(password)) {
			log.warn("NULL email or password in select User By Email And Password!");
			return null;
		}

		User user = null;
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn.prepareStatement("SELECT * FROM " + TABLE
					+ " WHERE (us_email)  = (?) AND (us_password)  = (?) ;");

			prepStat.setString(1, email);
			prepStat.setString(2, password);
			prepStat.execute();
			ResultSet res = prepStat.getResultSet();
			if (res.first()) {
				Field[] fields = objectClass.getDeclaredFields();
				AccessibleObject.setAccessible(fields, true);
				user = new User();
				for (Field f : fields) {
					setFieldFromResultSet(res, user, PREFIX, f);
				}
			}
		} catch (SQLException e) {
			log.error("in select user by email and password)", e);
		} catch (NoSuchMethodException e) {
			log.error("in select user by email and password)", e);
		} catch (IllegalAccessException e) {
			log.error("in select user by email and password)", e);
		} catch (InvocationTargetException e) {
			log.error("in select user by email and password)", e);
		} finally {
			DataAccessObject.silentClose(conn);
		}
		return user;
	}

	public  void updateUser(User user) {
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn.prepareStatement("UPDATE " + TABLE + "set" + " us_ownerId = ?, us_name = ?, "
					+ " us_birthDate = ?, us_email = ?, us_password = ?, "
					+ "us_degree = ?, us_cardCode = ?, us_right = ? " + " where us_id = ?");

			prepStat.setLong(1, user.getOwnerId());
			prepStat.setString(2, user.getName());
			prepStat.setLong(3, user.getBirthDate());
			prepStat.setString(4, user.getEmail());
			prepStat.setString(5, user.getPassword());
			prepStat.setString(6, user.getDegree());
			prepStat.setString(7, user.getCardCode());
			prepStat.setShort(8, user.getRight());
			prepStat.setLong(9, user.getId());
			prepStat.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccessObject.silentClose(conn);
		}
	}

	public void deleteUser(User user) {
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			if (user.getId() != null) {
				prepStat = conn.prepareStatement("delete from " + TABLE + "where " + PREFIX + "ID = ?");
				prepStat.setLong(1, user.getId());
				prepStat.execute();
			} else {
				log.error("Tried to delete user with 0 id" + user);
			}
		} catch (SQLException e) {
			log.error("in delete user ", e);
		} finally {
			DataAccessObject.silentClose(conn);
		}
	}

}
